﻿Imports System.Data.OleDb
Public Class frmManageShifts

#Region "NotifyIcon"

    Private Sub frmManageShifts_Resize(sender As Object, e As EventArgs) Handles Me.Resize
        Try
            If Me.WindowState = FormWindowState.Minimized Then
                Me.Visible = False
                NotifyIcon.Visible = True
                NotifyIcon.ShowBalloonTip(1, "SkyCoffee", "SkyCoffee is Still Running!", ToolTipIcon.Info)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub NotifyIcon_MouseDoubleClick(sender As Object, e As MouseEventArgs) Handles NotifyIcon.MouseDoubleClick
        Try
            Me.Visible = True
            Me.WindowState = FormWindowState.Normal
            NotifyIcon.Visible = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub mnuLaunchApplication_Click(sender As Object, e As EventArgs) Handles mnuLaunchApplication.Click
        Try
            Me.Visible = True
            Me.WindowState = FormWindowState.Normal
            NotifyIcon.Visible = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub mnuExitApplication_Click(sender As Object, e As EventArgs) Handles mnuExitApplication.Click
        Application.Exit()
    End Sub
#End Region

    Dim ds As New DataSet()
    Dim intCurrentIndex As Integer = 0
    Dim da As New OleDbDataAdapter()
    Dim conn As New OleDbConnection()

    Dim dsStaff As New DataSet()
    Dim intCurrentIndexStaff As Integer = 0
    Dim daStaff As New OleDbDataAdapter()
    Dim connStaff As New OleDbConnection()
    Private Sub frmManageShifts_Load(sender As Object, e As EventArgs) Handles Me.Load
        'Loads frmMenu
        pnlIcons.Controls.Clear()
        pnlIcons.Controls.Add(frmMenu.pnlIcons)

        'Load ToolTips
        AdminToolTips()

        'Clears ListView before it is populated to avoid duplicate records
        lstDisplayStaff.Items.Clear()

        'Populates ListView from Access Database
        Try
            PopulateStaffBasicListFromAccess(lstDisplayStaff)
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try

        'Enter data into tblShifts
        conn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data Source=|DataDirectory|\SkyCoffee.mdb"

        da.SelectCommand = New OleDbCommand("SELECT * FROM tblShifts")
        da.SelectCommand.Connection = conn


        da.UpdateCommand = New OleDbCommand("UPDATE tblShifts SET fldShiftStartTime = @fldShiftStartTime, fldShiftEndTime = @fldShiftEndTime, fldShiftTotalTime = @fldShiftTotalTime, fldShiftDescription = @fldShiftDescription WHERE fldShiftID = @fldShiftID")
        da.UpdateCommand.Connection = conn
        da.UpdateCommand.Parameters.Add("@fldShiftStartTime", OleDbType.VarChar, 40, "fldShiftStartTime")
        da.UpdateCommand.Parameters.Add("@fldShiftEndTime", OleDbType.VarChar, 40, "fldShiftEndTime")
        da.UpdateCommand.Parameters.Add("@fldShiftTotalTime", OleDbType.VarChar, 40, "fldShiftTotalTime")
        da.UpdateCommand.Parameters.Add("@fldShiftDescription", OleDbType.VarChar, 40, "fldShiftDescription")

        da.UpdateCommand.Parameters.Add("@fldShiftID", OleDbType.Integer, 6, "fldShiftID") '.SourceVersion = DataRowVersion.Original

        da.InsertCommand = New OleDbCommand("INSERT INTO tblShifts(fldShiftStartTime, fldShiftEndTime, fldShiftTotalTime, fldShiftDescription, fldShiftID) VALUES(@fldShiftStartTime,@fldShiftEndTime,@fldShiftTotalTime,@fldShiftDescription,@fldShiftID)")
        da.InsertCommand.Connection = conn
        da.InsertCommand.Parameters.Add("@fldShiftStartTime", OleDbType.VarChar, 40, "fldShiftStartTime")
        da.InsertCommand.Parameters.Add("@fldShiftEndTime", OleDbType.VarChar, 40, "fldShiftEndTime")
        da.InsertCommand.Parameters.Add("@fldShiftTotalTime", OleDbType.VarChar, 40, "fldShiftTotalTime")
        da.InsertCommand.Parameters.Add("@fldShiftDescription", OleDbType.VarChar, 40, "fldShiftDescription")

        da.InsertCommand.Parameters.Add("@fldShiftID", OleDbType.Numeric, 6, "fldShiftID")

        da.DeleteCommand = New OleDbCommand("DELETE FROM tblShifts WHERE fldShiftID = @fldShiftID")
        da.DeleteCommand.Connection = conn
        da.DeleteCommand.Parameters.Add("@fldShiftID", OleDbType.Integer, 6, "fldShiftID")

        da.Fill(ds)

        If ds.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFields()
        Else
            MsgBox("No records exist in database")
        End If


        'Enter data into tblShiftStaff
        connStaff.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data Source=|DataDirectory|\SkyCoffee.mdb"

        daStaff.SelectCommand = New OleDbCommand("SELECT * FROM tblShiftStaff")
        daStaff.SelectCommand.Connection = connStaff

        daStaff.UpdateCommand = New OleDbCommand("UPDATE tblShiftStaff SET fldStaffNumber = @fldStaffNumber, fldStaffFullName = @fldStaffFullName, fldStaffMobile = @fldStaffMobile WHERE fldShiftID = @fldShiftID")
        daStaff.UpdateCommand.Connection = connStaff
        daStaff.UpdateCommand.Parameters.Add("@fldStaffNumber", OleDbType.VarChar, 40, "fldStaffNumber")
        daStaff.UpdateCommand.Parameters.Add("@fldStaffFullName", OleDbType.VarChar, 40, "fldStaffFullName")
        daStaff.UpdateCommand.Parameters.Add("@fldStaffMobile", OleDbType.VarChar, 40, "fldStaffMobile")

        daStaff.UpdateCommand.Parameters.Add("@fldShiftID", OleDbType.Numeric, 6, "fldShiftID")

        daStaff.InsertCommand = New OleDbCommand("INSERT INTO tblShiftStaff(fldStaffNumber, fldStaffFullName, fldStaffMobile, fldShiftID) VALUES(@fldStaffNumber,@fldStaffFullName,@fldStaffMobile,@fldShiftID)")
        daStaff.InsertCommand.Connection = connStaff
        daStaff.InsertCommand.Parameters.Add("@fldStaffNumber", OleDbType.VarChar, 40, "fldStaffNumber")
        daStaff.InsertCommand.Parameters.Add("@fldStaffFullName", OleDbType.VarChar, 40, "fldStaffFullName")
        daStaff.InsertCommand.Parameters.Add("@fldStaffMobile", OleDbType.VarChar, 40, "fldStaffMobile")

        daStaff.InsertCommand.Parameters.Add("@fldShiftID", OleDbType.Numeric, 6, "fldShiftID")


        daStaff.DeleteCommand = New OleDbCommand("DELETE FROM tblShiftStaff WHERE fldShiftID = @fldShiftID")
        daStaff.DeleteCommand.Connection = connStaff
        daStaff.DeleteCommand.Parameters.Add("@fldShiftID", OleDbType.Numeric, 6, "fldShiftID")

        daStaff.Fill(dsStaff)

        If dsStaff.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFieldsStaff()
        Else
            MsgBox("No records exist in database")
        End If

        'Load tblShifts into ComboBox
        Dim connString As String
        Dim connShiftInformation As New System.Data.OleDb.OleDbConnection()
        Dim daShiftInformation As New System.Data.OleDb.OleDbDataAdapter()
        Dim dsShiftInformation As DataSet

        connString = "Provider=Microsoft.jet.oledb.4.0;" & _
            "Data Source=|DataDirectory|\SkyCoffee.mdb"

        connShiftInformation = New System.Data.OleDb.OleDbConnection(connString)
        connShiftInformation.Open()
        daShiftInformation = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM tblShifts", connShiftInformation)
        dsShiftInformation = New DataSet("Combo")
        daShiftInformation.Fill(dsShiftInformation, "Combo")
        cmbShiftInformation.DataSource = dsShiftInformation.Tables("Combo")

        cmbShiftInformation.LoadData() 'Load the ComboBox functions
    End Sub
    Public Sub AddNewRecord()

        'Reset form fields
        txtShiftID.Text = ""
        txtShiftStartTime.Text = ""
        txtShiftEndTime.Text = ""
        txtShiftTotalTime.Text = ""
        txtShiftDescription.Text = ""

        txtShiftStartTime.Focus()

        'Generate Random Reference Number
        Dim intIDLength As Integer  'length of ID
        Dim intCount As Integer     'variable to count to the value of intIDLength
        Dim intAsc As Integer       'random number to choose what the character will be

        Dim strResult As String     'value of the resulting ID

        Dim intNumber As Integer
        Dim intCharacter As Integer 'the resulting character from one cycle

        intIDLength = 3             'length of shift id

        Randomize()

        For intNumber = 1 To 1

            'number of random characters to generate
            For intCount = 1 To intIDLength


                'choose random character type to generate numbers
                intAsc = Int((1 * Rnd()) + 1)

                Select Case intAsc

                    Case 1
                        intCharacter = Int((Asc("9") - Asc("0") + 1) * Rnd() + Asc("0"))

                End Select

                'concatenate the resulting character to the previously generated character
                strResult = strResult & Chr(intCharacter)

                'repeat process if the value of "intCount" has not reached "intNumberDigits"
            Next intCount

            strResult = strResult

        Next intNumber

        'display the resulting id in the label
        txtShiftID.Text = strResult

    End Sub
    Public Sub AddNewRecordStaff()
        'Reset form fields
        cmbShiftInformation.Value = ""
        txtStaffNumber.Text = ""
        txtStaffFullName.Text = ""
        txtStaffMobile.Text = ""

        cmbShiftInformation.Focus()

        'Clears ListView before it is populated to avoid duplicate records
        lstDisplayStaff.Items.Clear()

        'Populates ListView from Access Database
        Try
            PopulateStaffBasicListFromAccess(lstDisplayStaff)
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Public Sub FillFields()
        txtShiftID.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldShiftID").ToString()
        txtShiftStartTime.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldShiftStartTime").ToString()
        txtShiftEndTime.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldShiftEndTime").ToString()
        txtShiftTotalTime.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldShiftTotalTime").ToString()
        txtShiftDescription.Text = ds.Tables(0).Rows(intCurrentIndex).Item("fldShiftDescription").ToString()
    End Sub
    Public Sub FillFieldsStaff()
        cmbShiftInformation.Value = dsStaff.Tables(0).Rows(intCurrentIndexStaff).Item("fldShiftID").ToString()
        txtStaffNumber.Text = dsStaff.Tables(0).Rows(intCurrentIndexStaff).Item("fldStaffNumber").ToString()
        txtStaffFullName.Text = dsStaff.Tables(0).Rows(intCurrentIndexStaff).Item("fldStaffFullName").ToString()
        txtStaffMobile.Text = dsStaff.Tables(0).Rows(intCurrentIndexStaff).Item("fldStaffMobile").ToString()
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        If txtShiftStartTime.Text = "" Or txtShiftEndTime.Text = "" Or txtShiftDescription.Text = "" Then
            MsgBox("Please fill in all of the fields")
        Else

            'Calculate ShiftTotalTime
            Dim strShiftTotalTime As String
            Dim strShiftStartTime As String
            Dim strShiftEndTime As String

            strShiftStartTime = txtShiftStartTime.Text
            strShiftEndTime = txtShiftEndTime.Text

            strShiftTotalTime = strShiftEndTime - strShiftStartTime

            txtShiftTotalTime.Text = strShiftTotalTime / 100


            'Database Operations: Add all data to Access Database
            Dim dr As DataRow

            dr = ds.Tables(0).NewRow()  'Gets a reference to a new row.
            dr("fldShiftID") = txtShiftID.Text

            dr("fldShiftStartTime") = txtShiftStartTime.Text
            dr("fldShiftEndTime") = txtShiftEndTime.Text
            dr("fldShiftTotalTime") = txtShiftTotalTime.Text
            dr("fldShiftDescription") = txtShiftDescription.Text

            ds.Tables(0).Rows.Add(dr)
            da.Update(ds)
            ds.AcceptChanges()

            AddNewRecord()

            MsgBox("Record added successfully")
        End If
    End Sub

    Private Sub btnShiftStaffAdd_Click(sender As Object, e As EventArgs) Handles btnShiftStaffAdd.Click
        If txtStaffNumber.Text = "" Or txtStaffFullName.Text = "" Then
            MsgBox("Please fill in all of the fields")
        Else

            'Database Operations: Add all data to Access Database
            Dim drStaff As DataRow

            drStaff = dsStaff.Tables(0).NewRow()  'Gets a reference to a new row.
            drStaff("fldShiftID") = cmbShiftInformation.Value

            drStaff("fldStaffNumber") = txtStaffNumber.Text
            drStaff("fldStaffFullName") = txtStaffFullName.Text
            drStaff("fldStaffMobile") = txtStaffMobile.Text

            dsStaff.Tables(0).Rows.Add(drStaff)
            daStaff.Update(dsStaff)
            dsStaff.AcceptChanges()

            AddNewRecordStaff()

            MsgBox("Record added successfully")
        End If
    End Sub

    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        AddNewRecord()
    End Sub

    Private Sub btnShiftStaffCancel_Click(sender As Object, e As EventArgs) Handles btnShiftStaffCancel.Click
        AddNewRecordStaff()
    End Sub

    Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
        If ds.Tables(0).Rows.Count > 1 Then
            'Since 0 is the first row
            intCurrentIndex = 0
            FillFields()
        ElseIf ds.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the first record.")
        End If
    End Sub

    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
        If intCurrentIndex > 0 Then  'We move back only if we're not at the first row.
            intCurrentIndex = intCurrentIndex - 1  'Subtract one from the current index.
            FillFields()
        ElseIf ds.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the first record.")
        End If
    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then 'ds.Tables(0).Rows.Count - 1 is the index for the last row
            intCurrentIndex = intCurrentIndex + 1  'Add one to the current index.
            FillFields()
        ElseIf ds.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the last record.")
        End If
    End Sub

    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
        If ds.Tables(0).Rows.Count > 1 Then
            intCurrentIndex = ds.Tables(0).Rows.Count - 1 'ds.Tables(0).Rows.Count - 1 is the index for the last row
            FillFields()
        ElseIf ds.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the last record.")
        End If
    End Sub

    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        AddNewRecord()
    End Sub

    Private Sub btnShiftStaffClear_Click(sender As Object, e As EventArgs) Handles btnShiftStaffClear.Click
        AddNewRecordStaff()
    End Sub

    Private Sub btnShiftStaffFirst_Click(sender As Object, e As EventArgs) Handles btnShiftStaffFirst.Click
        If dsStaff.Tables(0).Rows.Count > 1 Then
            'Since 0 is the first row
            intCurrentIndexStaff = 0
            FillFieldsStaff()
        ElseIf dsStaff.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the first record.")
        End If
    End Sub

    Private Sub btnShiftStaffPrevious_Click(sender As Object, e As EventArgs) Handles btnShiftStaffPrevious.Click
        If intCurrentIndexStaff > 0 Then  'We move back only if we're not at the first row.
            intCurrentIndexStaff = intCurrentIndexStaff - 1  'Subtract one from the current index.
            FillFieldsStaff()
        ElseIf dsStaff.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the first record.")
        End If
    End Sub

    Private Sub btnShiftStaffNext_Click(sender As Object, e As EventArgs) Handles btnShiftStaffNext.Click
        If intCurrentIndexStaff < dsStaff.Tables(0).Rows.Count - 1 Then 'ds.Tables(0).Rows.Count - 1 is the index for the last row
            intCurrentIndexStaff = intCurrentIndexStaff + 1  'Add one to the current index.
            FillFieldsStaff()
        ElseIf dsStaff.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the last record.")
        End If
    End Sub

    Private Sub btnShiftStaffLast_Click(sender As Object, e As EventArgs) Handles btnShiftStaffLast.Click
        If dsStaff.Tables(0).Rows.Count > 1 Then
            intCurrentIndexStaff = dsStaff.Tables(0).Rows.Count - 1 'ds.Tables(0).Rows.Count - 1 is the index for the last row
            FillFieldsStaff()
        ElseIf dsStaff.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        Else
            MessageBox.Show("You are already at the last record.")
        End If
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        If txtShiftStartTime.Text = "" Or txtShiftEndTime.Text = "" Or txtShiftDescription.Text = "" Then
            MsgBox("Please fill in all of the fields")
        Else

            'Calculate ShiftTotalTime
            Dim strShiftTotalTime As String
            Dim strShiftStartTime As String
            Dim strShiftEndTime As String

            strShiftStartTime = txtShiftStartTime.Text
            strShiftEndTime = txtShiftEndTime.Text

            strShiftTotalTime = strShiftEndTime - strShiftStartTime

            txtShiftTotalTime.Text = strShiftTotalTime / 100

            'Database Operations: Add all data to Access Database
            Dim dr As DataRow

            dr = ds.Tables(0).Rows(intCurrentIndex) 'This gets a reference to the row currently being edited
            dr.BeginEdit()
            dr("fldShiftID") = txtShiftID.Text

            dr("fldShiftStartTime") = txtShiftStartTime.Text
            dr("fldShiftEndTime") = txtShiftEndTime.Text
            dr("fldShiftTotalTime") = txtShiftTotalTime.Text
            dr("fldShiftDescription") = txtShiftDescription.Text
            dr.EndEdit()

            da.Update(ds)  'Ask the dataadapter to call the UpdateCommand and update the database
            ds.AcceptChanges() 'Commits the change to the dataset.

            AddNewRecord()

            MsgBox("Record updated successfully")
        End If
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim MsgBoxDelete As MsgBoxResult = MsgBox("Are You Sure You Wish To Delete This ?", MsgBoxStyle.YesNo)

        If ds.Tables(0).Rows.Count > 0 Then

            If MsgBoxDelete = MsgBoxResult.Yes Then

                Dim dr As DataRow

                dr = ds.Tables(0).Rows(intCurrentIndex)
                dr.Delete() 'Delete the row

                da.Update(ds)
                ds.AcceptChanges()

                MsgBox("Record removed successfully")

            Else
                MsgBox("Record not removed")
            End If
        ElseIf ds.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        End If
    End Sub

    Private Sub btnShiftStaffUpdate_Click(sender As Object, e As EventArgs) Handles btnShiftStaffUpdate.Click
        If txtStaffNumber.Text = "" Or txtStaffFullName.Text = "" Then
            MsgBox("Please fill in all of the fields")
        Else

            'Database Operations: Add all data to Access Database
            Dim drStaff As DataRow

            drStaff = dsStaff.Tables(0).Rows(intCurrentIndexStaff) 'This gets a reference to the row currently being edited
            drStaff.BeginEdit()
            drStaff("fldShiftID") = cmbShiftInformation.Value

            drStaff("fldStaffNumber") = txtStaffNumber.Text
            drStaff("fldStaffFullName") = txtStaffFullName.Text
            drStaff("fldStaffMobile") = txtStaffMobile.Text
            drStaff.EndEdit()

            daStaff.Update(dsStaff)  'Ask the dataadapter to call the UpdateCommand and update the database
            dsStaff.AcceptChanges() 'Commits the change to the dataset.

            AddNewRecordStaff()

            MsgBox("Record updated successfully")
        End If
    End Sub

    Private Sub btnShiftStaffDelete_Click(sender As Object, e As EventArgs) Handles btnShiftStaffDelete.Click
        Dim MsgBoxDelete As MsgBoxResult = MsgBox("Are You Sure You Wish To Delete This ?", MsgBoxStyle.YesNo)

        If dsStaff.Tables(0).Rows.Count > 0 Then

            If MsgBoxDelete = MsgBoxResult.Yes Then

                Dim drStaff As DataRow

                drStaff = dsStaff.Tables(0).Rows(intCurrentIndexStaff)
                drStaff.Delete() 'Delete the row

                daStaff.Update(dsStaff)
                dsStaff.AcceptChanges()

                MsgBox("Record removed successfully")

            Else
                MsgBox("Record not removed")
            End If
        ElseIf dsStaff.Tables(0).Rows.Count = 0 Then
            MsgBox("No records exist in database")
        End If
    End Sub

    Private Sub txtShiftStartTime_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtShiftStartTime.KeyPress
        Select Case e.KeyChar
            Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", vbBack '// your pre-selected Characters and Backspace.
                e.Handled = False '// allow.
            Case Else
                e.Handled = True '// block.

                MsgBox("You can only enter Numbers in this TextBox")
                txtShiftStartTime.Focus()
        End Select
    End Sub

    Private Sub txtShiftEndTime_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtShiftEndTime.KeyPress
        Select Case e.KeyChar
            Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", vbBack '// your pre-selected Characters and Backspace.
                e.Handled = False '// allow.
            Case Else
                e.Handled = True '// block.

                MsgBox("You can only enter Numbers in this TextBox")
                txtShiftEndTime.Focus()
        End Select
    End Sub
End Class